#!/usr/bin/php
<?php
/**
 +----------------------------------------------------------
 * [标准版转换成基础物料版]升级总脚本
 * 
 * 注意：
 *    a). 只支持PHP版本5.3，请注意php5.6版本不支持；
 *    b). 升级前请先备份数据库
 *    c). 升级前,必需先处理掉复审订单、跨境订单 和 部分拆分、部分发货的订单；
 *    d). 当捆绑商品货号 与 普通商品货号 相同时，升级转换脚本会自动跳过（需注意）；
 *    e). 升级完成后，后台--》系统配置，如：订单复审配置、自动审单配置、拆单配置、CRM赠品、发货配置等都将被重置；
 *    f). 只针对标准版3.8.0 升迁至 物料版4.0.1；
 *    g). 代码版本需要对应升级（例如：标准线third第三方仓储版==只能对应升级到==》物料线上的third第三方仓储版）
 *    h). 脚本执行完成后，需要进入app/base目录下输入命令（例如：/usr/local/php-pthreads/bin/php ./cmd update）强制刷新表结构；
 +----------------------------------------------------------
 * Author: wangbiao@shopex.cn
 * Time: 2016-04-20 $
 * [Ecos!] (C)2003-2016 Shopex Inc.
 +----------------------------------------------------------
 */
error_reporting(E_ALL ^ E_NOTICE);


$domain = $argv[1];
if(empty($domain))
{
    die('No Params');
}


set_time_limit(0);
require_once(dirname(__FILE__) . '/../../lib/init.php');
cachemgr::init(false);


$db     = kernel::database();

/*------------------------------------------------------ */
//-- 客户确认是否已经备份过数据库？
/*------------------------------------------------------ */
printf("You Confirm That You Have Backed Up The Database, Yes/No?");
printf("\n>");

$answer    = read_input();

if(strtolower($answer) != 'yes')
{
    die('Termination Of Execution!');
}



/*------------------------------------------------------------ */
//-- [升级前]需要先处理掉复审订单、跨境订单 和 部分拆分、部分发货的订单
/*------------------------------------------------------------ */
$sql    = "SELECT count(*) as nums FROM sdb_ome_orders WHERE process_status='is_retrial'";
$count  = $db->selectrow($sql);
$total  = $count['nums'];
if($total)
{
    $msg    = '还有' . $total .'个复审订单，请先处理完成再进行升级；';
    ilog($msg, false, 'upgrade_product_to_material');
    die($msg);
}

$sql    = "SELECT count(*) as nums FROM sdb_ome_orders WHERE process_status='is_declare'";
$count  = $db->selectrow($sql);
$total  = $count['nums'];
if($total)
{
    $msg    = '还有' . $total .'个跨境申报订单，请先处理完成再进行升级；';
    ilog($msg, false, 'upgrade_product_to_material');
    die($msg);
}

$sql    = "SELECT count(*) as nums FROM sdb_ome_orders WHERE (process_status='splitting' OR ship_status='2') AND status='active'";
$count  = $db->selectrow($sql);
$total  = $count['nums'];
if($total)
{
    $msg    = '还有' . $total .'个订单是部分拆分、部分发货状态，请先处理完成再进行升级；';
    ilog($msg, false, 'upgrade_product_to_material');
    die($msg);
}



/*------------------------------------------------------ */
//-- 自动安装、卸载物料版对应不需要的App应用
//-- 请输入您的代码版本（pro:旗舰版, wms:自有仓储版, third:第三方仓储版）
/*------------------------------------------------------ */
printf("Please Enter Your Version, pro OR wms OR third?");
printf("\n>");

$version    = read_input();
$version    = trim(strtolower($version));

if($version == 'wms')
{
    #卸载App
    $uninstall_app    = array('tgkpi', 'channel', 'wangwang', 'crm', 'openapi', 'omevirtualwms', 'invoice', 'drm', 'finance', 'monitor', 'replacesku', 'suitclient');
    foreach ($uninstall_app as $key => $val)
    {
        if(app::get($val)->is_installed())
        {
            kernel::single('base_shell_webproxy')->exec_command('uninstall '. $val);
        }
    }
    
    #安装APP
    $install_app    = array('material');
    foreach ($install_app as $key => $val)
    {
        if(app::get($val)->is_installed() != true)
        {
            kernel::single('base_shell_webproxy')->exec_command('install '. $val);
        }
    }
}
elseif($version == 'third') 
{
    #卸载App
    $uninstall_app    = array('drm', 'finance', 'wangwang', 'monitor', 'replacesku', 'invoice', 'crm', 'openapi', 'tgkpi', 'channel', 'suitclient');
    foreach ($uninstall_app as $key => $val)
    {
        if(app::get($val)->is_installed())
        {
            kernel::single('base_shell_webproxy')->exec_command('uninstall '. $val);
        }
    }
    
    #安装APP
    $install_app    = array('material');
    foreach ($install_app as $key => $val)
    {
        if(app::get($val)->is_installed() != true)
        {
            kernel::single('base_shell_webproxy')->exec_command('install '. $val);
        }
    }
}
elseif($version == 'pro') 
{
    #卸载App
    $uninstall_app    = array('drm', 'finance', 'wangwang', 'monitor', 'replacesku', 'suitclient');
    foreach ($uninstall_app as $key => $val)
    {
        if(app::get($val)->is_installed())
        {
            kernel::single('base_shell_webproxy')->exec_command('uninstall '. $val);
        }
    }
    
    #自动增加物流公司类型字段
    $sql    = "alter table sdb_ome_dly_corp add d_type tinyint(1) default 1 comment '线上/线下业务区分标记'";
    $db->exec($sql);
    
    #安装APP
    $install_app    = array('material', 'organization', 'o2o', 'wap');
    foreach ($install_app as $key => $val)
    {
        if(app::get($val)->is_installed() != true)
        {
            kernel::single('base_shell_webproxy')->exec_command('install '. $val);
        }
    }
}
else 
{
    die('Input Version Error...');
}
unset($uninstall_app, $install_app);



/*------------------------------------------------------ */
//-- 隐藏"商品管理"、"捆绑商品"、"店铺冻结明细"导航菜单栏目
/*------------------------------------------------------ */
$sql    = "UPDATE `sdb_desktop_menus` SET display='false' 
           WHERE (app_id='ome' AND workground='goods_manager' AND menu_group='商品管理') 
           OR (app_id='omepkg') OR (app_id='console' AND permission='ome_shop_freeze_log')";
$db->exec($sql);

/*------------------------------------------------------ */
//-- 全局App更新
/*------------------------------------------------------ */
kernel::single('base_shell_webproxy')->exec_command("update --ignore-download");

#刷新全局services(services.xml无变化，执行没效果)
//kernel::single('base_shell_webproxy')->exec_command("update --ignore-download --force-update-services");

#强制更新所有Desktop
kernel::single('base_shell_webproxy')->exec_command("update --ignore-download --force-update-desktop");

/*------------------------------------------------------ */
//-- [初始化]先清空物料相关数据库表
/*------------------------------------------------------ */
$clear_data    = array(
                    'sdb_material_basic_material',
                    'sdb_material_basic_material_combination_items',
                    'sdb_material_basic_material_conf',
                    'sdb_material_basic_material_conf_special',
                    'sdb_material_basic_material_ext',
                    'sdb_material_basic_material_feature_group',
                    'sdb_material_basic_material_stock',
                    'sdb_material_basic_material_storage_life',
                    'sdb_material_basic_material_storage_life_bills',
                    'sdb_material_codebase',
                    'sdb_material_sales_basic_material',
                    'sdb_material_sales_material',
                    'sdb_material_sales_material_ext',
                    'sdb_material_sales_material_shop_freeze',
                );
if($clear_data)
{
    foreach ($clear_data as $key => $val)
    {
        $sql    = "truncate table " . $val;
        $db->exec($sql);
    }
}



/*------------------------------------------------------ */
//-- 实例化
/*------------------------------------------------------ */
$salesMaterialObj = app::get('material')->model('sales_material');
$salesMaterialExtObj = app::get('material')->model('sales_material_ext');
$salesBasicMaterialObj = app::get('material')->model('sales_basic_material');
$pkgObj = app::get('omepkg')->model('pkg_goods');
$pkgProductObj = app::get('omepkg')->model('pkg_product');



/*第一步*/
/*------------------------------------------------------ */
//-- 批量转换货品为基础物料
/*------------------------------------------------------ */
echo("\n\nstart step 1....\n\n");

$suffix    = 'batch_products_material';

$total   = 0;
$succ    = 0;
$fail    = 0;

$page     = 0;#开始页
$limit    = 100;#每次执行数量

$sql    = "SELECT count(*) as nums FROM sdb_ome_products WHERE 1=1 ";
$count  = $db->selectrow($sql);
$total    = $count['nums'];
if(empty($total))
{
    ilog('没有查找到相关货品.', false, $suffix);
    exit;#商品表无数据_全部结束
}
else 
{
    $result    = batch_convert_material();
    
    ilog('第一步,批量货品转基础物料已完成, 共计'.$total.'个，失败'.$fail.'个', true, $suffix);
}

//货品转基础物料方法
function batch_convert_material()
{
    global $db, $page, $limit, $fail, $suffix;
    
    $insert_list    = array();
    $barcode_list   = array();
    $bm_ids         = array();
    $barcode_data   = array();#过滤重复的条形码
    $special_list   = array();
    
    $field  = 'p.product_id, p.name, p.bn, p.cost, p.price, p.weight, p.unit, p.store, p.store_freeze, p.alert_store, p.barcode, p.visibility, p.spec_info, g.type_id, g.brand_id, g.serial_number';
    $sql    = "SELECT ". $field ." FROM sdb_ome_products AS p 
               LEFT JOIN sdb_ome_goods AS g ON p.goods_id=g.goods_id 
               WHERE 1=1 LIMIT ". ($page * $limit) .", " . $limit;
    $dataList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($dataList))
    {
        return false;
    }
    
    #拼接数据
    foreach ($dataList as $key => $val)
    {
        $product_id    = $val['product_id'];
        $product_name  = str_replace("'", "", $val['name']);#替换单引号
        $product_bn    = addslashes($val['bn']);#转义危险字符
        
        $product_bn_crc32    = sprintf('%u', crc32($val['bn']));
        $serial_number       = ($val['serial_number'] == 'true' ? 'true' : 'false');#唯一码
        $visibled            = ($val['visibility'] == 'false' ? 2 : 1);
        
        $cost          = floatval($val['cost']);
        $retail_price  = floatval($val['price']);
        $weight        = floatval($val['weight']);
        $unit          = $val['unit'];
        
        #新增扩展字段(物料规格、品牌id、商品类型)
        $specifications    = (strlen($val['spec_info']) > 255 ? '' : $val['spec_info']);
        $brand_id          = intval($val['brand_id']);
        $cat_id            = intval($val['type_id']);
        
        $store         = intval($val['store']);
        $store_freeze  = intval($val['store_freeze']);
        $alert_store   = intval($val['alert_store']);
        
        $insert_list['material'][$product_id]        = "('". $product_id ."','". $product_name ."','". $product_bn ."','". $product_bn_crc32 ."','". $serial_number ."','". $visibled ."','". time() ."')";
        $insert_list['material_ext'][$product_id]    = "('". $product_id ."','". $cost ."','". $retail_price ."','". $weight ."','". $unit ."','". $specifications ."','". $brand_id ."','". $cat_id ."')";
        $insert_list['material_stock'][$product_id]  = "('". $product_id ."','". $store ."','". $store_freeze ."','". $alert_store ."')";
        
        #条形码[过滤重复的条形码]
        if($val['barcode'] && !in_array($val['barcode'], $barcode_data))
        {
            $barcode_list['product_id'][$product_id]    = $product_id;
            $barcode_list['barcode'][$product_id]       = "('". $product_id ."',1,'". str_replace("'", "", $val['barcode']) ."')";
            
            $barcode_data[]    = $val['barcode'];
        }
        else 
        {
            #物料条码必填_条形码为空则使用货号
            $barcode_list['product_id'][$product_id]    = $product_id;
            $barcode_list['barcode'][$product_id]       = "('". $product_id ."',1,'". $product_bn ."')";
            
            //$barcode_data[]    = $product_bn;
        }
        
        #初始化特殊扫码配置
        $special_list[$product_id]    = "('". $product_id ."','4','1','1')";
        
        $bm_ids[$product_id]    = $product_id;
    }
    
    #销毁
    unset($barcode_data);
    
    #如果货品已存在基础物料中则不处理[注：product_id唯一值]
    $dataList    = array();
    if($bm_ids)
    {
        $sql    = "SELECT bm_id FROM sdb_material_basic_material WHERE bm_id in(". implode(',', $bm_ids) .")";
        $dataList    = $db->select($sql);
    }
    
    if($dataList)
    {
        foreach ($dataList as $key => $val)
        {
            $bm_id    = $val['bm_id'];
            
            if(isset($insert_list['material'][$bm_id]))
            {
                unset($insert_list['material'][$bm_id]);
                unset($insert_list['material_ext'][$bm_id]);
                unset($insert_list['material_stock'][$bm_id]);
                
                unset($bm_ids[$bm_id]);
                
                $fail++;
            }
        }
    }
    
    #加入事务
    $db->beginTransaction();
    
    #插入数据
    if($insert_list['material'])
    {
        #主表
        $insert_sql    = "INSERT INTO `sdb_material_basic_material` (`bm_id`,`material_name`,`material_bn`,`material_bn_crc32`,`serial_number`,`visibled`,`create_time`) VALUES";
        $insert_sql    .= implode(',', $insert_list['material']);
        $result    = $db->exec($insert_sql);
        if($result)
        {
            #扩展表
            $insert_sql    = "INSERT INTO `sdb_material_basic_material_ext` (`bm_id`,`cost`,`retail_price`,`weight`,`unit`,`specifications`,`brand_id`,`cat_id`) VALUES";
            $insert_sql    .= implode(',', $insert_list['material_ext']);
            $result    = $db->exec($insert_sql);
            if($result)
            {
                #库存表
                $insert_sql    = "INSERT INTO `sdb_material_basic_material_stock` (`bm_id`,`store`,`store_freeze`,`alert_store`) VALUES";
                $insert_sql    .= implode(',', $insert_list['material_stock']);
                $result    = $db->exec($insert_sql);
                if(!$result)
                {
                    $db->rollBack();
                    
                    ilog('插入库存表出错, 查询起始值:'.($page * $limit), false, $suffix);
                    
                    echo('Warning: INSERT sdb_material_basic_material_stock...Error...');
                    exit;
                }
            }
            else 
            {
                $db->rollBack();
                
                ilog('插入扩展表出错, 查询起始值:'.($page * $limit), false, $suffix);
                
                echo('Warning: INSERT sdb_material_basic_material_ext...Error...');
                exit;
            }
        }
        else 
        {
            $db->rollBack();
            
            ilog('插入主表出错, 查询起始值:'.($page * $limit), false, $suffix);
            
            echo('Warning: INSERT sdb_material_basic_material...Error...');
            exit;
        }
    }
    
    #转换至销售物料
    if($result)
    {
        $params    = implode(',', $bm_ids);
        $result    = kernel::single('material_basic_exchange')->process($params);
    }
    
    #[判断]条形码是否已存在
    $dataList    = array();
    if($barcode_list['product_id'])
    {
        $sql    = "SELECT bm_id FROM sdb_material_codebase WHERE bm_id in(". implode(',', $barcode_list['product_id']) .")";
        $dataList    = $db->select($sql);
    }
    
    if($dataList)
    {
        foreach ($dataList as $key => $val)
        {
            $bm_id    = $val['bm_id'];
        
            if(isset($barcode_list['barcode'][$bm_id]))
            {
                unset($barcode_list['barcode'][$bm_id]);
                unset($barcode_list['product_id'][$bm_id]);
            }
        }
    }
    
    #[插入]条形码关联基础物料
    if($barcode_list['barcode'])
    {
        $insert_sql    = "INSERT INTO `sdb_material_codebase` (`bm_id`,`type`,`code`) VALUES";
        $insert_sql    .= implode(',', $barcode_list['barcode']);
        $result    = $db->exec($insert_sql);
        
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: INSERT sdb_material_codebase...Error...');
            exit;
        }
    }
    
    #[插入]初始化特殊扫码配置
    if($special_list)
    {
        $insert_sql    = "INSERT INTO `sdb_material_basic_material_conf_special` (`bm_id`,`openscan`,`fromposition`,`toposition`) VALUES";
        $insert_sql    .= implode(',', $special_list);
        $result    = $db->exec($insert_sql);
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: INSERT sdb_material_basic_material_conf_special...Error...');
            exit;
        }
    }
    
    
    #提交事务
    $db->commit();
    
    unset($dataList, $insert_list, $barcode_list);
    
    $page++;
    usleep(1000000);#暂停一秒
    
    #递归调用
    batch_convert_material();
}



/*第二步*/
/*------------------------------------------------------ */
//-- 批量转换原有的捆绑商品
//-- 注意：当捆绑商品货号 与 普通商品货号 相同时，升级转换脚本会自动跳过；
/*------------------------------------------------------ */
echo("start step 2....\n\n");

$suffix    = 'batch_pkg_goods';

$pkg_total   = 0;
$pkg_succ    = 0;
$pkg_fail    = 0;

$pkg_page     = 0;#开始页
$pkg_limit    = 100;#每次执行数量

transPkg();
ilog('第二步,批量转换捆绑商品已完成, 共计'.$pkg_total.'个，成功'.$pkg_succ.'个，失败'.$pkg_fail.'个', true, $suffix);

function transPkg()
{
    global $db, $pkg_page, $pkg_limit, $pkg_total, $pkg_succ, $pkg_fail, $suffix, 
           $salesMaterialObj, $salesMaterialExtObj, $salesBasicMaterialObj, $pkgObj, $pkgProductObj;
    
    $pkgList = $pkgObj->getList('*', array(), ($pkg_page * $pkg_limit), $pkg_limit);
    
    #没有可执行的捆绑商品
    if(empty($pkgList))
    {
        return false;
    }

        //合计总共多少个捆绑商品
        $pkg_total += count($pkgList);

        foreach($pkgList as $pkg){
            $old_pkg_key = md5($pkg['pkg_bn']);
            $old_pkg_arr[$old_pkg_key] = $pkg;
            $old_pkg_bns[] = $pkg['pkg_bn'];
        }

        //根据查询出的货号判断是否已被占用，占用的捆绑数据过滤不处理
        $salesMExist = $salesMaterialObj->getList('sales_material_bn', array('sales_material_bn'=>$old_pkg_bns), 0, -1);
        foreach($salesMExist as $exist)
        {
            if(in_array($exist['sales_material_bn'], $old_pkg_bns)){
                //存在就过滤
                $exist_key = md5($exist['sales_material_bn']);
                unset($old_pkg_arr[$exist_key]);
                $pkg_fail++;
                ilog('查询的捆绑商品货号：'.$exist['sales_material_bn'].'已被占用, 查询起始值：'.($pkg_page * $pkg_limit), false, $suffix);
            }
        }
        
        #加入事务
        $db->beginTransaction();

        //剩余的捆绑做转换保存
        foreach($old_pkg_arr as $old_pkg){
            
            $old_pkg['name']      = str_replace("'", "", $old_pkg['name']);#替换单引号
            $old_pkg['pkg_bn']    = addslashes($old_pkg['pkg_bn']);#转义危险字符
            
            $sales_material_bn_crc32 = sprintf('%u',crc32($old_pkg['pkg_bn']));
            $new_pkg_data = array(
                'shop_id' => '_ALL_',
                'sales_material_name' => $old_pkg['name'],
                'sales_material_bn' => $old_pkg['pkg_bn'],
                'sales_material_bn_crc32' => $sales_material_bn_crc32,
                'sales_material_type' => 2,
                'create_time' => time(),
            );

            $is_save = $salesMaterialObj->save($new_pkg_data);
            if($is_save){
                //保存原来的捆绑货品的扩展信息
                $new_pkg_ext_data = array(
                    'sm_id' => $new_pkg_data['sm_id'],
                    'cost' => 0.00,
                    'retail_price' => 0.00,
                    'weight' => $old_pkg['weight'] ? $old_pkg['weight'] : 0.00,
                    'unit' => '',
                );
                $inser_flag    = $salesMaterialExtObj->insert($new_pkg_ext_data);
                
                #事务回滚
                if(!$inser_flag)
                {
                    $db->rollBack();
                    
                    echo('Warning: INSERT sdb_material_sales_material_ext...Error...');
                    exit;
                }

                //获取原来的捆绑商品绑定的货品信息做转换
                $old_pkg_product_arr = $pkgProductObj->getList('*', array('goods_id'=>$old_pkg['goods_id']), 0, -1);
                if($old_pkg_product_arr)
                {
                    //if(count($old_pkg_product_arr) > 1)
                    //{
                        $pkg_product_list     = array();
                        $pkg_product_count    = 0;
                        foreach ($old_pkg_product_arr as $key => $val)
                        {
                            $pkg_product_list[$val['product_id']]    = array('pkgnum'=>$val['pkgnum'], 'percent'=>0);
                            
                            $pkg_product_count += $val['pkgnum'];
                        }
                    //}
                    
                    $percent    = 100;
                    $rate       = 100;#默认
                    $loop       = 1;
                    foreach($old_pkg_product_arr as $old_pkg_product)
                    {
                        $pkg_product_id    = $old_pkg_product['product_id'];
                        
                        #计算贡献占比
                        if($pkg_product_list)
                        {
                            if($loop == count($old_pkg_product_arr))
                            {
                                $pkg_product_list[$pkg_product_id]['percent']    = $percent;
                            }
                            else
                            {
                                $diff_num    = round(($pkg_product_list[$pkg_product_id]['pkgnum']/$pkg_product_count), 2);
                                $diff_num    = intval($diff_num * 10) * 10;
                                $pkg_product_list[$pkg_product_id]['percent']    = $diff_num;
                                
                                $percent    = $percent - $diff_num;
                                $percent    = ($percent < 0 ? 0 : $percent);
                            }
                            $loop++;
                            
                            $rate    = intval($pkg_product_list[$pkg_product_id]['percent']);#取整10的倍数
                        }
                        
                        #插入
                        $addBindData = array(
                            'sm_id' => $new_pkg_data['sm_id'],
                            'bm_id' => $old_pkg_product['product_id'],
                            'number' => $old_pkg_product['pkgnum'],
                            'rate' => $rate,
                        );
                        $inser_flag    = $salesBasicMaterialObj->insert($addBindData);
                        
                        #事务回滚
                        if(!$inser_flag)
                        {
                            $db->rollBack();
                            
                            echo('Warning: INSERT sdb_material_sales_basic_material...Error...');
                            exit;
                        }
                        
                        $addBindData = null;
                    }

                    $salesMaterialObj->update(array('is_bind'=>1),array('sm_id'=>$new_pkg_data['sm_id']));
                }

                $pkg_succ++;
            }else{
                $pkg_fail++;
            }
        }

    #提交事务
    $db->commit();
    
    #release
    unset($pkgList, $salesMExist, $old_pkg_product_arr);
    
    $pkg_page++;
    usleep(1000000);#暂停一秒
    
    transPkg();
}



/*第三步*/
/*淘宝赠品、crm赠品在原有基础上增加开启关闭状态，默认为关闭，老的设置没用需人工重新设置*/
echo("start step 3....\n\n");



/*第四步*/
/*------------------------------------------------------ */
//-- 批量货品冻结库存_转换到_基础物料冻结库存
/*------------------------------------------------------ */
echo("start step 4....\n\n");

$suffix    = 'batch_basic_material_stock';

$total   = 0;
$succ    = 0;
$fail    = 0;

$page     = 0;#开始页
$limit    = 500;#每次执行数量

$sql    = "SELECT count(*) as nums FROM sdb_ome_orders 
           WHERE process_status in('unconfirmed', 'splitting', 'splited', 'is_retrial', 'is_declare') 
           AND status='active' AND ship_status in('0', '2', '3')";
$count  = $db->selectrow($sql);
$total    = $count['nums'];
if(empty($total))
{
    ilog('没有查找到相关订单.', false, $suffix);
}
else 
{
    #首先，设置“基础物料冻结库存”全部为0
    $update_sql    = "UPDATE sdb_material_basic_material_stock SET store_freeze=0 WHERE 1=1";
    $db->exec($update_sql);
    
    #批量转换
    $result    = batch_convert_material_store_freeze();
    
    ilog('第四步,批量基础物料冻结库存已完成, 共计'.$total.'个订单', true, $suffix);
}

/**
 * 未确认、部分拆分的订单对应货品冻结库存转换
 * 
 * @return boolean
 */
function batch_convert_material_store_freeze()
{
    global $db, $page, $limit, $suffix;
    
    $sql    = "SELECT o.order_id, i.item_id, i.product_id, i.bn, i.nums, i.sendnum 
               FROM sdb_ome_orders AS o 
               LEFT JOIN sdb_ome_order_items AS i ON o.order_id=i.order_id 
               WHERE o.process_status in('unconfirmed', 'splitting', 'splited', 'is_retrial', 'is_declare')
               AND o.status='active' AND o.ship_status in('0', '2', '3') AND i.nums > i.sendnum AND i.`delete`= 'false' AND i.product_id>0 
               LIMIT ". ($page * $limit) .", " . $limit;
    $dataList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($dataList))
    {
        return false;
    }
    
    #拼接数据
    $store_freeze_list    = array();
    $bm_ids    = array();
    
    foreach ($dataList as $key => $val)
    {
        $product_id    = $val['product_id'];
        $nums          = intval($val['nums']) - intval($val['sendnum']);#未拆分的订单货品数量
        
        $store_freeze_list[$product_id]    = intval($store_freeze_list[$product_id]) + $nums;
        $bm_ids[$product_id]    = $product_id;
    }
    
    #只更新存在的基础物料冻结库存
    $dataList    = array();
    if($bm_ids)
    {
        $sql    = "SELECT bm_id FROM sdb_material_basic_material_stock WHERE bm_id in(". implode(',', $bm_ids) .")";
        $dataList    = $db->select($sql);
    }
    
    if($dataList)
    {
        #加入事务
        $db->beginTransaction();
        
        foreach ($dataList as $key => $val)
        {
            $bm_id    = $val['bm_id'];
            
            $update_sql    = "UPDATE sdb_material_basic_material_stock
                              SET store_freeze=store_freeze+". intval($store_freeze_list[$bm_id]) ."
                              WHERE bm_id=". $bm_id;
            $flag    = $db->exec($update_sql);
            
            #事务回滚
            if(!$flag)
            {
                $db->rollBack();
                
                echo('Warning: UPDATE sdb_material_basic_material_stock...Error...');
                exit;
            }
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($dataList, $store_freeze_list, $bm_ids);
    
    $page++;
    usleep(1000000);#暂停一秒
    
    #递归调用
    batch_convert_material_store_freeze();
}



/*第五步*/
/*------------------------------------------------------ */
//-- Step1.批量更新失败订单对象表（失败订单 并且 未匹配到销售物料）
/*------------------------------------------------------ */
echo("start step 5.1....\n\n");

$suffix    = 'batch_fail_orders_material';

$total   = 0;
$succ    = 0;
$fail    = 0;

$page     = 0;#开始页
$limit    = 500;#每次执行数量

$sql    = "SELECT count(*) as nums FROM sdb_ome_orders AS a 
           LEFT JOIN sdb_ome_order_objects AS b ON a.order_id=b.order_id 
           WHERE a.is_fail='true' AND a.status='active'";
$count  = $db->selectrow($sql);
$total    = $count['nums'];
if(empty($total))
{
    ilog('没有查找到订单.', false, $suffix);
}
else 
{
    $result    = batch_convert_fail_orders();
    
    ilog('第五步5.1,批量更新失败订单对象表已完成, 共计'.$total.'个，失败'.$fail.'个', true, $suffix);
}

/*
 * 订单明细批量转换方法
*
*/
function batch_convert_fail_orders()
{
    global $db, $page, $limit, $fail, $suffix;

    #查询失败订单
    $sql    = "SELECT a.order_id, a.order_bn, a.status, a.is_fail, b.obj_id, b.obj_type, b.goods_id, b.bn 
               FROM sdb_ome_orders AS a 
               LEFT JOIN sdb_ome_order_objects AS b ON a.order_id=b.order_id 
               WHERE a.is_fail='true' AND a.status='active' 
               LIMIT ". ($page * $limit) .", " . $limit;
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }
    
    #加入事务
    $db->beginTransaction();
    
    foreach ($orderList as $key => $val)
    {
        #获取订单货品bn对应销售物料[需判断销售物料类型sales_material_type_防止捆绑商品货号与普通商品货号相同]
        $sql    = "SELECT sm_id FROM sdb_material_sales_material WHERE sales_material_bn='". $val['bn'] ."' AND is_bind=1";
        $sql    .= ($val['obj_type'] == 'pkg' ? " AND sales_material_type=2" : " AND sales_material_type=1");
        
        $sales_material    = $db->selectrow($sql);
        if(empty($sales_material))
        {
                #更新objects表中goods_id=0
                $sql    = "UPDATE `sdb_ome_order_objects` SET goods_id=0 WHERE obj_id=". $val['obj_id'];
                $is_save    = $db->exec($sql);
                if(!$is_save)
                {
                    $db->rollBack();
                    
                    echo('Warning: UPDATE sdb_ome_order_objects AND goods_id=0...Error...');
                    exit;
                }
                
                #删除关联items表中记录
                $sql    = "DELETE FROM sdb_ome_order_items where order_id=". $val['order_id'] ." AND obj_id=". $val['obj_id'];
                $is_save    = $db->exec($sql);
                if(!$is_save)
                {
                    $db->rollBack();
                    
                    echo('Warning: DELETE sdb_ome_order_items...Error...');
                    exit;
                }
                
                ilog('失败订单号：'. $val['order_bn'] .', 未找到商品BN：'.$val['bn'], false, $suffix.'_notbn');
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $is_save, $sales_material);
    
    $page++;
    //ilog('Step 5.1,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_convert_fail_orders();
}



/*------------------------------------------------------ */
//-- Step2.批量更新订单对象表[无规格_商品对应单个商品]
/*------------------------------------------------------ */
echo("start step 5.2....\n\n");

$suffix    = 'batch_orders_material';
$total   = 0;
$succ    = 0;
$fail    = 0;

$page     = 0;#开始页
$limit    = 500;#每次执行数量

$result    = batch_convert_orders();

ilog('第五步5.2,批量转换订单明细已完成, 失败'.$fail.'个', true, $suffix);

/*
 * 订单明细批量转换方法
*
*/
function batch_convert_orders()
{
    global $db, $page, $limit, $fail, $suffix;

    #查询订单
    $sql    = "SELECT obj_id, obj_type, goods_id, bn FROM sdb_ome_order_objects WHERE 1=1 GROUP BY bn, obj_type 
               LIMIT ". ($page * $limit) .", " . $limit;
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }
    
    #加入事务
    $db->beginTransaction();
    
    foreach ($orderList as $key => $val)
    {
        #获取订单货品bn对应销售物料[需判断销售物料类型sales_material_type_防止捆绑商品货号与普通商品货号相同]
        $sql    = "SELECT sm_id FROM sdb_material_sales_material WHERE sales_material_bn='". $val['bn'] ."' AND is_bind=1";
        $sql    .= ($val['obj_type'] == 'pkg' ? " AND sales_material_type=2" : " AND sales_material_type=1");
        
        $sales_material    = $db->selectrow($sql);
        if(empty($sales_material))
        {
            ilog('obj_type：'. $val['obj_type'] .', 未找到商品BN：'.$val['bn'], false, $suffix.'_notbn');
            
            $fail++;
            continue;
        }
        
        #转换order_objects对象表对应的销售物料信息
        $sql        = "UPDATE `sdb_ome_order_objects` SET goods_id='". $sales_material['sm_id'] ."' 
                       WHERE bn='". $val['bn'] ."' AND obj_type='". $val['obj_type'] ."'";
        $is_save    = $db->exec($sql);
        if(!$is_save)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_ome_order_objects step 5.2...Error...');
            exit;
        }
        
        #保存更新的货号
        ilog('obj_type：'. $val['obj_type'] .', BN：'.$val['bn'], true, $suffix.'_page');
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $is_save, $sales_material);
    
    $page++;
    //ilog('Step 5.2,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_convert_orders();
}



/*------------------------------------------------------ */
//-- Step 5.3: 一个goods型普通objects层对应多个普通order_items
//-- **************************************************
//-- 以objects层上的bn  新建一个***_sys_pkg后缀的促销销售物料
//-- objects层上的quantity购买数量更新为1
//-- **************************************************
/*------------------------------------------------------ */
echo("start step 5.3....\n\n");

$suffix    = 'batch_sys_create_object_bn';
$total   = 0;
$succ    = 0;
$fail    = 0;

$page     = 0;#开始页
$limit    = 500;#每次执行数量

$result    = batch_sys_create_object_bn();

ilog('第五步5.3,批量转换订单明细已完成, 失败'.$fail.'个', true, $suffix);

/*
 * 订单明细批量转换方法
*
*/
function batch_sys_create_object_bn()
{
    global $db, $page, $limit, $fail, $suffix;
    
    #查询订单（注：limit固定500数量，bn、goods_id、obj_type会被更新）
    $sql    = "SELECT b.obj_id, b.bn AS obj_bn, b.name FROM sdb_ome_order_items AS a 
               LEFT JOIN sdb_ome_order_objects AS b ON a.obj_id=b.obj_id 
               WHERE a.item_type IN('product', 'gift') GROUP BY a.obj_id HAVING COUNT(a.obj_id)>1 
               LIMIT 0, 500";
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }
    
    #加入事务
    $db->beginTransaction();
    
    $obj_ids    = array();
    foreach ($orderList as $key => $val)
    {
        //系统自动生成pkg促销销售物料
        $sales_material    = sys_create_pkg_sales_material($val);
        if(!$sales_material)
        {
            #事务回滚
            $db->rollBack();
            
            echo('Warning: create sdb_material_pkg_sales_material...Error...');
            exit;
        }
        
        //更新order_objects层上bn、goods_id、quantity、obj_type
        $update_sql    = "UPDATE `sdb_ome_order_objects` 
                          SET goods_id=". $sales_material['sm_id'] .", bn='". $sales_material['sales_material_bn'] ."', quantity=1, obj_type='pkg' 
                          WHERE obj_id=". $val['obj_id'];
        $result    = $db->exec($update_sql);
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_ome_order_objects step 5.3...Error...');
            exit;
        }
        
        //obj_id
        $obj_ids[]    = $val['obj_id'];
    }
    
    #[统一]更新order_items层上item_type为捆绑类型
    if($obj_ids)
    {
        $update_sql    = "UPDATE `sdb_ome_order_items` SET item_type='pkg' WHERE obj_id IN(". implode(',', $obj_ids) .")";
        $result    = $db->exec($update_sql);
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_ome_order_items step 5.3...Error...');
            exit;
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $sales_material, $result, $update_sql, $obj_ids);
    
    $page++;
    //ilog('Step 5.3,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_sys_create_object_bn();
}



/*------------------------------------------------------ */
//-- Step 5.4.批量更新订单对象表
//-- 有规格_商品对应多个货品时
//-- order_objects层上obj_bn没有对应销售物料
/*------------------------------------------------------ */
echo("start step 5.4...\n\n");

$suffix    = 'batch_convert_orders_objects';
$total   = 0;
$succ    = 0;
$fail    = 0;
$page    = 0;#开始页

$result    = batch_convert_orders_objects();

ilog('第五步5.4,批量更新订单对象表已完成, 失败'.$fail.'个', true, $suffix);

/*
 * 订单明细批量转换方法
*
*/
function batch_convert_orders_objects()
{
    global $db, $fail, $suffix, $page;

    #查询订单（注：limit固定500数量，bn、goods_id会被更新）
    $sql    = "SELECT a.obj_id, c.bn AS item_bn FROM sdb_ome_order_objects AS a 
               LEFT JOIN sdb_ome_order_items AS c ON a.obj_id=c.obj_id 
               WHERE a.obj_type IN('goods', 'gift') AND a.bn in(SELECT bn FROM sdb_ome_goods WHERE spec_desc != '') AND c.bn!='' 
               GROUP BY c.obj_id LIMIT 0, 500";
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }
    
    #加入事务
    $db->beginTransaction();
    
    foreach ($orderList as $key => $val)
    {
        #根据item_bn查找对应的销售物料
        $sql    = "SELECT sm_id, sales_material_bn FROM sdb_material_sales_material WHERE sales_material_bn='". $val['item_bn'] ."'";
        $sales_material    = $db->selectrow($sql);
        
        if($sales_material)
        {
            #更新objects层数据
            $update_sql    = "UPDATE `sdb_ome_order_objects` SET goods_id=". $sales_material['sm_id'] .", bn='". $sales_material['sales_material_bn'] ."' 
                              WHERE obj_id=". $val['obj_id'];
            $result    = $db->exec($update_sql);
            if(!$result)
            {
                $db->rollBack();
                
                echo('Warning: UPDATE sdb_ome_order_objects step 5.4...Error...');
                exit;
            }
        }
        else
        {
            //系统自动生成普通销售物料
            $sales_material    = sys_create_normal_sales_material($val);
            if(!$sales_material)
            {
                #事务回滚
                $db->rollBack();
                
                echo('Warning: create sdb_material_normal_sales_material...Error...');
                exit;
            }
            
            //更新order_objects层上bn、goods_id
            $update_sql    = "UPDATE `sdb_ome_order_objects` SET goods_id=". $sales_material['sm_id'] .", bn='". $sales_material['sales_material_bn'] ."' 
                              WHERE obj_id=". $val['obj_id'];
            $result    = $db->exec($update_sql);
            if(!$result)
            {
                $db->rollBack();
                
                echo('Warning: UPDATE sdb_ome_order_objects step 5.4...Error...');
                exit;
            }
            
            ilog('第五步5.4,销售物料bn: '. $val['item_bn'] .' 不存在, 系统自动新建销售物料号:'. $sales_material['sales_material_bn'] .'并匹配', false, $suffix.'_create');
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $sales_material, $result, $update_sql);
    
    $page++;
    //ilog('Step 5.4,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_convert_orders_objects();
}



/*------------------------------------------------------ */
//-- Step 5.5.批量更新订单对象表上不存在的销售物料bn
//-- order_objects上bn为空 或者 不是销售物料的转换
//-- 普通商品及赠品类型: goods、gift
/*------------------------------------------------------ */
echo("start step 5.5...\n\n");

$suffix    = 'batch_noexist_orders_objects';
$total   = 0;
$succ    = 0;
$fail    = 0;
$page    = 0;

$result    = batch_noexist_orders_objects();

ilog('第五步5.5,批量更新订单对象表已完成', true, $suffix);

/*
 * 订单明细批量转换方法
*
*/
function batch_noexist_orders_objects()
{
    global $db, $fail, $suffix, $page;
    
    #查询不存在销售物料的订单（注：limit固定500数量，bn、goods_id会被更新）
    $sql    = "SELECT a.obj_id, c.bn AS item_bn FROM sdb_ome_order_objects AS a 
               LEFT JOIN sdb_ome_order_items AS c ON a.obj_id=c.obj_id 
               WHERE a.obj_type IN('goods', 'gift') 
               AND a.bn NOT IN (SELECT sales_material_bn FROM sdb_material_sales_material WHERE sales_material_type=1) AND c.bn!='' 
               LIMIT 0, 500";
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }

    #加入事务
    $db->beginTransaction();
    
    foreach ($orderList as $key => $val)
    {
        #根据item_bn查找对应的销售物料
        $sql    = "SELECT sm_id, sales_material_bn FROM sdb_material_sales_material WHERE sales_material_bn='". $val['item_bn'] ."'";
        $sales_material    = $db->selectrow($sql);
        
        //不存在,系统自动生成普通销售物料
        if(empty($sales_material))
        {
            $sales_material    = sys_create_normal_sales_material($val);
            if(!$sales_material)
            {
                $db->rollBack();
                
                echo('Warning: create sdb_material_normal_sales_material...Error...');
                exit;
            }
            
            ilog('第五步5.5,销售物料bn: '. $val['item_bn'] .' 不存在, 系统自动新建销售物料号:'. $sales_material['sales_material_bn'] .'并匹配', false, $suffix.'_create');
        }
        
        #更新objects层数据
        $update_sql    = "UPDATE `sdb_ome_order_objects` SET goods_id=". $sales_material['sm_id'] .", bn='". $sales_material['sales_material_bn'] ."' 
                          WHERE obj_id=". $val['obj_id'];
        $result    = $db->exec($update_sql);
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_ome_order_objects step 5.5...Error...');
            exit;
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $sales_material, $result, $update_sql);
    
    $page++;
    //ilog('Step 5.5,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_noexist_orders_objects();
}



/*------------------------------------------------------ */
//-- Step 5.6.批量更新订单对象表上不存在的销售物料bn
//-- order_objects上bn为空 或者 不是销售物料的转换
//-- 捆绑商品: pkg
/*------------------------------------------------------ */
echo("start step 5.6...\n\n");

$suffix    = 'batch_noexist_pkg_orders_objects';
$total   = 0;
$succ    = 0;
$fail    = 0;
$page    = 0;

$result    = batch_noexist_pkg_orders_objects();

ilog('第五步5.6,批量更新订单对象表已完成', true, $suffix);

/*
 * 订单明细批量转换方法
*
*/
function batch_noexist_pkg_orders_objects()
{
    global $db, $fail, $suffix, $page;
    
    #查询不存在销售物料的订单（注：limit固定500数量，bn、goods_id会被更新）
    $sql    = "SELECT a.obj_id, a.bn AS obj_bn, a.name FROM sdb_ome_order_objects AS a 
               WHERE a.obj_type='pkg' 
               AND a.bn NOT IN (SELECT sales_material_bn FROM sdb_material_sales_material WHERE sales_material_type=2) 
               LIMIT 0, 500";
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }

    #加入事务
    $db->beginTransaction();
    
    foreach ($orderList as $key => $val)
    {
        //系统自动生成pkg促销销售物料
        $sales_material    = sys_create_pkg_sales_material($val);
        if(!$sales_material)
        {
            $db->rollBack();
            
            echo('Warning: create sdb_material_pkg_sales_material...Error...');
            exit;
        }
        
        //更新order_objects层上bn、goods_id、quantity
        $update_sql    = "UPDATE `sdb_ome_order_objects` 
                          SET goods_id=". $sales_material['sm_id'] .", bn='". $sales_material['sales_material_bn'] ."', quantity=1 
                          WHERE obj_id=". $val['obj_id'];
        $result    = $db->exec($update_sql);
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_ome_order_objects step 5.6...Error...');
            exit;
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $sales_material, $result, $update_sql);
    
    $page++;
    //ilog('Step 5.6,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_noexist_pkg_orders_objects();
}




/*第六步*/
/*------------------------------------------------------ */
//-- 检验库存深度中店铺货品是否存在于销售物料列表中,不存在则解绑
/*------------------------------------------------------ */
echo("start step 6....\n\n");

$suffix    = 'batch_convert_mapping_inventorydepth';

$total   = 0;
$succ    = 0;
$fail    = 0;

$page     = 0;#开始页
$limit    = 100;#每次执行数量

$sql    = "SELECT count(*) as nums FROM sdb_inventorydepth_shop_skus WHERE mapping='1'";
$count  = $db->selectrow($sql);
$total    = $count['nums'];

if(empty($total))
{
    ilog('没有查找到已绑定的店铺货品.', false, $suffix);
}
else
{
    $result    = batch_convert_mapping_inventorydepth();
    
    ilog('第六步,批量库存深度中店铺货品检验绑定已完成, 共计'.$total.'个店铺货品已绑定', true, $suffix);
}

/*
 * 库存深度中店铺货品与销售物料匹配
*
*/
function batch_convert_mapping_inventorydepth()
{
    global $db, $page, $limit, $suffix;
    
    $sql    = "SELECT id, shop_id, shop_product_bn, bind
               FROM sdb_inventorydepth_shop_skus WHERE mapping='1'
               LIMIT ". ($page * $limit) .", " . $limit;
    $dataList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($dataList))
    {
        return false;
    }
    
    #加入事务
    $db->beginTransaction();
    
    foreach ($dataList as $key => $val)
    {
        $shop_id            = $val['shop_id'];
        $shop_product_bn    = $val['shop_product_bn'];
    
        $sql    = "SELECT sm_id, sales_material_bn, sales_material_type
                   FROM sdb_material_sales_material WHERE sales_material_bn='". $shop_product_bn ."'
                   AND is_bind=1 AND shop_id in('_ALL_', '". $shop_id ."')";
        $row    = $db->selectrow($sql);
        
        #更新
        if($row)
        {
            #绑定
            $bind    = ($row['sales_material_type'] == 2 ? '1' : '0');#是否捆绑商品
            $update_sql    = "UPDATE sdb_inventorydepth_shop_skus SET mapping='1', bind='". $bind ."' WHERE id='". $val['id'] ."'";
        }
        else
        {
            #取消绑定
            $update_sql    = "UPDATE sdb_inventorydepth_shop_skus SET mapping='0', bind='0', update_time='". time() ."' WHERE id='". $val['id'] ."'";
            
            ilog('第六步,取消绑定的销售物料BN：'. $row['sales_material_bn'], false, $suffix.'_reset');
        }
        $result    = $db->exec($update_sql);
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_inventorydepth_shop_skus...Error...');
            exit;
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($dataList, $row, $sql, $update_sql, $result);
    
    $page++;
    usleep(1000000);#暂停一秒
    
    #递归调用
    batch_convert_mapping_inventorydepth();
}



/*第七步*/
/*------------------------------------------------------ */
//-- 批量更新销售物料店铺冻结
/*------------------------------------------------------ */
echo("start step 7....\n\n");

#[初始化]先清空店铺冻结表数据
$sql    = "truncate table sdb_material_sales_material_shop_freeze";
$db->exec($sql);

$suffix    = 'batch_sales_material_shop_freeze';

$total   = 0;
$succ    = 0;
$fail    = 0;

$page     = 0;#开始页
$limit    = 500;#每次执行数量

$result    = batch_sales_material_shop_freeze();

ilog('第七步,批量更新销售物料店铺冻结已完成, 共失败 '. $fail .'个', true, $suffix);

/*
 * 计算销售物料的店铺冻结数量
*
*/
function batch_sales_material_shop_freeze()
{
    global $db, $page, $limit, $fail, $suffix;
    
    #查询
    $sql    = "SELECT o.shop_id, j.goods_id 
               FROM sdb_ome_order_objects AS j LEFT JOIN sdb_ome_orders AS o ON j.order_id=o.order_id 
               WHERE o.process_status in ('unconfirmed', 'is_retrial', 'is_declare', 'splitting') 
               AND o.ship_status in('0', '2') AND o.status='active' group by o.shop_id, j.goods_id LIMIT ". ($page * $limit) .", " . $limit;
    $dataList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($dataList))
    {
        return false;
    }
    
    #加入事务
    $db->beginTransaction();
    
    foreach ($dataList as $key => $val)
    {
        if(empty($val['shop_id']) || empty($val['goods_id']))
        {
            ilog('第七步,未执行_不符合条件...shop_id:'. $val['shop_id'] .', sm_id:'. $val['goods_id'], false, $suffix);
            
            continue;
        }
        
        #计算店铺冻结数量
        $result    = update_sales_material_shop_freeze($val['shop_id'], $val['goods_id']);
        
        if(!$result)
        {
            $db->rollBack();
            
            $fail++;
            ilog('第七步,执行失败...shop_id:'. $val['shop_id'] .', sm_id:'. $val['goods_id'], false, $suffix);
            
            echo('Warning: UPDATE sdb_material_sales_material_shop_freeze...Error...');
            exit;
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($dataList, $sql, $result);
    
    $page++;
    usleep(1000000);#暂停一秒
    
    #递归调用
    batch_sales_material_shop_freeze();
}



/*------------------------------------------------------ */
//-- Step8.1 [归档]批量更新订单对象表[无规格_商品对应单个商品]
/*------------------------------------------------------ */
echo("\n\nstart step 8.1....\n\n");

$suffix    = 'batch_archive_order_objects';
$total   = 0;
$succ    = 0;
$fail    = 0;

$page     = 0;#开始页
$limit    = 500;#每次执行数量

$result    = batch_archive_order_objects();

ilog('第八步8.1,批量转换归档订单明细已完成, 失败'.$fail.'个', true, $suffix);

/*
 * 订单明细批量转换方法
*
*/
function batch_archive_order_objects()
{
    global $db, $page, $limit, $fail, $suffix;

    #查询订单
    $sql    = "SELECT obj_id, obj_type, goods_id, bn FROM sdb_archive_order_objects WHERE 1=1 GROUP BY bn, obj_type 
               LIMIT ". ($page * $limit) .", " . $limit;
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }
    
    #加入事务
    $db->beginTransaction();
    
    foreach ($orderList as $key => $val)
    {
        #获取订单货品bn对应销售物料[需判断销售物料类型sales_material_type_防止捆绑商品货号与普通商品货号相同]
        $sql    = "SELECT sm_id FROM sdb_material_sales_material WHERE sales_material_bn='". $val['bn'] ."' AND is_bind=1";
        $sql    .= ($val['obj_type'] == 'pkg' ? " AND sales_material_type=2" : " AND sales_material_type=1");
        
        $sales_material    = $db->selectrow($sql);
        if(empty($sales_material))
        {
            ilog('obj_type：'. $val['obj_type'] .', 未找到商品BN：'.$val['bn'], false, $suffix.'_notbn');
            
            $fail++;
            continue;
        }
        
        #转换order_objects对象表对应的销售物料信息
        $sql        = "UPDATE `sdb_archive_order_objects` SET goods_id='". $sales_material['sm_id'] ."' 
                       WHERE bn='". $val['bn'] ."' AND obj_type='". $val['obj_type'] ."'";
        $is_save    = $db->exec($sql);
        if(!$is_save)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_archive_order_objects step 8.1...Error...');
            exit;
        }
        
        #保存更新的货号
        ilog('obj_type：'. $val['obj_type'] .', BN：'.$val['bn'], true, $suffix.'_page');
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $is_save, $sales_material);
    
    $page++;
    //ilog('Step 8.1,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_archive_order_objects();
}




/*------------------------------------------------------ */
//-- Step 8.2: [归档]一个goods型普通objects层对应多个普通order_items
//-- **************************************************
//-- 以objects层上的bn  新建一个***_sys_pkg后缀的促销销售物料
//-- objects层上的quantity购买数量更新为1
//-- **************************************************
/*------------------------------------------------------ */
echo("start step 8.2....\n\n");

$suffix    = 'batch_archive_sys_create_object_bn';
$total   = 0;
$succ    = 0;
$fail    = 0;

$page     = 0;#开始页
$limit    = 500;#每次执行数量

$result    = batch_archive_sys_create_object_bn();

ilog('第八步8.2,批量转换订单明细已完成, 失败'.$fail.'个', true, $suffix);

/*
 * 订单明细批量转换方法
*
*/
function batch_archive_sys_create_object_bn()
{
    global $db, $page, $limit, $fail, $suffix;
    
    #查询订单（注：limit固定500数量，bn、goods_id、obj_type会被更新）
    $sql    = "SELECT b.obj_id, b.bn AS obj_bn, b.name FROM sdb_archive_order_items AS a 
               LEFT JOIN sdb_archive_order_objects AS b ON a.obj_id=b.obj_id 
               WHERE a.item_type IN('product', 'gift') GROUP BY a.obj_id HAVING COUNT(a.obj_id)>1 
               LIMIT 0, 500";
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }
    
    #加入事务
    $db->beginTransaction();
    
    $obj_ids    = array();
    foreach ($orderList as $key => $val)
    {
        //系统自动生成pkg促销销售物料
        $sales_material    = archive_sys_create_pkg_sales_material($val);
        if(!$sales_material)
        {
            #事务回滚
            $db->rollBack();
            
            echo('Warning: create sdb_material_pkg_sales_material...Error...');
            exit;
        }
        
        //更新order_objects层上bn、goods_id、quantity、obj_type
        $update_sql    = "UPDATE `sdb_archive_order_objects` 
                          SET goods_id=". $sales_material['sm_id'] .", bn='". $sales_material['sales_material_bn'] ."', quantity=1, obj_type='pkg' 
                          WHERE obj_id=". $val['obj_id'];
        $result    = $db->exec($update_sql);
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_archive_order_objects step 8.2...Error...');
            exit;
        }
        
        //obj_id
        $obj_ids[]    = $val['obj_id'];
    }
    
    #[统一]更新order_items层上item_type为捆绑类型
    if($obj_ids)
    {
        $update_sql    = "UPDATE `sdb_archive_order_items` SET item_type='pkg' WHERE obj_id IN(". implode(',', $obj_ids) .")";
        $result    = $db->exec($update_sql);
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_archive_order_items step 8.2...Error...');
            exit;
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $sales_material, $result, $update_sql, $obj_ids);
    
    $page++;
    //ilog('Step 8.2,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_archive_sys_create_object_bn();
}




/*------------------------------------------------------ */
//-- Step 8.3.[归档]批量更新订单对象表
//-- 有规格_商品对应多个货品时
//-- order_objects层上obj_bn没有对应销售物料
/*------------------------------------------------------ */
echo("start step 8.3...\n\n");

$suffix    = 'batch_archive_orders_objects_by_goods';
$total   = 0;
$succ    = 0;
$fail    = 0;
$page    = 0;#开始页

$result    = batch_archive_orders_objects_by_goods();

ilog('第八步8.3,批量更新订单对象表已完成, 失败'.$fail.'个', true, $suffix);

/*
 * 订单明细批量转换方法
*
*/
function batch_archive_orders_objects_by_goods()
{
    global $db, $fail, $suffix, $page;

    #查询订单（注：limit固定500数量，bn、goods_id会被更新）
    $sql    = "SELECT a.obj_id, c.bn AS item_bn FROM sdb_archive_order_objects AS a 
               LEFT JOIN sdb_archive_order_items AS c ON a.obj_id=c.obj_id 
               WHERE a.obj_type IN('goods', 'gift') AND a.bn in(SELECT bn FROM sdb_ome_goods WHERE spec_desc != '') AND c.bn!='' 
               GROUP BY c.obj_id LIMIT 0, 500";
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }
    
    #加入事务
    $db->beginTransaction();
    
    foreach ($orderList as $key => $val)
    {
        #根据item_bn查找对应的销售物料
        $sql    = "SELECT sm_id, sales_material_bn FROM sdb_material_sales_material WHERE sales_material_bn='". $val['item_bn'] ."'";
        $sales_material    = $db->selectrow($sql);
        
        if($sales_material)
        {
            #更新objects层数据
            $update_sql    = "UPDATE `sdb_archive_order_objects` SET goods_id=". $sales_material['sm_id'] .", bn='". $sales_material['sales_material_bn'] ."' 
                              WHERE obj_id=". $val['obj_id'];
            $result    = $db->exec($update_sql);
            if(!$result)
            {
                $db->rollBack();
                
                echo('Warning: UPDATE sdb_archive_order_objects step 1.3...Error...');
                exit;
            }
        }
        else
        {
            //系统自动生成普通销售物料
            $sales_material    = archive_sys_create_normal_sales_material($val);
            if(!$sales_material)
            {
                #事务回滚
                $db->rollBack();
                
                echo('Warning: create sdb_material_normal_sales_material...Error...');
                exit;
            }
            
            //更新order_objects层上bn、goods_id
            $update_sql    = "UPDATE `sdb_archive_order_objects` SET goods_id=". $sales_material['sm_id'] .", bn='". $sales_material['sales_material_bn'] ."' 
                              WHERE obj_id=". $val['obj_id'];
            $result    = $db->exec($update_sql);
            if(!$result)
            {
                $db->rollBack();
                
                echo('Warning: UPDATE sdb_archive_order_objects step 8.3...Error...');
                exit;
            }
            
            ilog('第八步8.3,销售物料bn: '. $val['item_bn'] .' 不存在, 系统自动新建销售物料号:'. $sales_material['sales_material_bn'] .'并匹配', false, $suffix.'_create');
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $sales_material, $result, $update_sql);
    
    $page++;
    //ilog('Step 8.3,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_archive_orders_objects_by_goods();
}



/*------------------------------------------------------ */
//-- Step 8.4.[归档]批量更新订单对象表上不存在的销售物料bn
//-- order_objects上bn为空 或者 不是销售物料的转换
//-- 普通商品及赠品类型: goods、gift
/*------------------------------------------------------ */
echo("start step 8.4...\n\n");

$suffix    = 'batch_archive_noexist_orders_objects';
$total   = 0;
$succ    = 0;
$fail    = 0;
$page    = 0;

$result    = batch_archive_noexist_orders_objects();

ilog('第八步8.4,批量更新订单对象表已完成', true, $suffix);

/*
 * 订单明细批量转换方法
*
*/
function batch_archive_noexist_orders_objects()
{
    global $db, $fail, $suffix, $page;
    
    #查询不存在销售物料的订单（注：limit固定500数量，bn、goods_id会被更新）
    $sql    = "SELECT a.obj_id, c.bn AS item_bn FROM sdb_archive_order_objects AS a 
               LEFT JOIN sdb_archive_order_items AS c ON a.obj_id=c.obj_id 
               WHERE a.obj_type IN('goods', 'gift') 
               AND a.bn NOT IN (SELECT sales_material_bn FROM sdb_material_sales_material WHERE sales_material_type=1) AND c.bn!='' 
               LIMIT 0, 500";
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }

    #加入事务
    $db->beginTransaction();
    
    foreach ($orderList as $key => $val)
    {
        #根据item_bn查找对应的销售物料
        $sql    = "SELECT sm_id, sales_material_bn FROM sdb_material_sales_material WHERE sales_material_bn='". $val['item_bn'] ."'";
        $sales_material    = $db->selectrow($sql);
        
        //不存在,系统自动生成普通销售物料
        if(empty($sales_material))
        {
            $sales_material    = archive_sys_create_normal_sales_material($val);
            if(!$sales_material)
            {
                $db->rollBack();
                
                echo('Warning: create sdb_material_normal_sales_material...Error...');
                exit;
            }
            
            ilog('第五步8.4,销售物料bn: '. $val['item_bn'] .' 不存在, 系统自动新建销售物料号:'. $sales_material['sales_material_bn'] .'并匹配', false, $suffix.'_create');
        }
        
        #更新objects层数据
        $update_sql    = "UPDATE `sdb_archive_order_objects` SET goods_id=". $sales_material['sm_id'] .", bn='". $sales_material['sales_material_bn'] ."' 
                          WHERE obj_id=". $val['obj_id'];
        $result    = $db->exec($update_sql);
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_archive_order_objects step 8.4...Error...');
            exit;
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $sales_material, $result, $update_sql);
    
    $page++;
    //ilog('Step 8.4,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_archive_noexist_orders_objects();
}



/*------------------------------------------------------ */
//-- Step 8.5.[归档]批量更新订单对象表上不存在的销售物料bn
//-- order_objects上bn为空 或者 不是销售物料的转换
//-- 捆绑商品: pkg
/*------------------------------------------------------ */
echo("start step 8.5...\n\n");

$suffix    = 'batch_archive_noexist_pkg_orders_objects';
$total   = 0;
$succ    = 0;
$fail    = 0;
$page    = 0;

$result    = batch_archive_noexist_pkg_orders_objects();

ilog('第八步8.5,批量更新订单对象表已完成', true, $suffix);

/*
 * 订单明细批量转换方法
*
*/
function batch_archive_noexist_pkg_orders_objects()
{
    global $db, $fail, $suffix, $page;
    
    #查询不存在销售物料的订单（注：limit固定500数量，bn、goods_id会被更新）
    $sql    = "SELECT a.obj_id, a.bn AS obj_bn, a.name FROM sdb_archive_order_objects AS a 
               WHERE a.obj_type='pkg' 
               AND a.bn NOT IN (SELECT sales_material_bn FROM sdb_material_sales_material WHERE sales_material_type=2) 
               LIMIT 0, 500";
    $orderList    = $db->select($sql);
    
    #没有可执行的货品,则退出.
    if(empty($orderList))
    {
        return false;
    }

    #加入事务
    $db->beginTransaction();
    
    foreach ($orderList as $key => $val)
    {
        //系统自动生成pkg促销销售物料
        $sales_material    = archive_sys_create_pkg_sales_material($val);
        if(!$sales_material)
        {
            $db->rollBack();
            
            echo('Warning: create sdb_material_pkg_sales_material...Error...');
            exit;
        }
        
        //更新order_objects层上bn、goods_id、quantity
        $update_sql    = "UPDATE `sdb_archive_order_objects` 
                          SET goods_id=". $sales_material['sm_id'] .", bn='". $sales_material['sales_material_bn'] ."', quantity=1 
                          WHERE obj_id=". $val['obj_id'];
        $result    = $db->exec($update_sql);
        if(!$result)
        {
            $db->rollBack();
            
            echo('Warning: UPDATE sdb_archive_order_objects step 8.5...Error...');
            exit;
        }
    }
    
    #提交事务
    $db->commit();
    
    #release
    unset($orderList, $sql, $sales_material, $result, $update_sql);
    
    $page++;
    //ilog('Step 8.5,Executed Page '. $page, true, $suffix.'_page');
    
    usleep(500000);#暂停0.5秒
    
    #递归调用
    batch_archive_noexist_pkg_orders_objects();
}




#脚本执行完成
echo("...Success, Upgrade completed...\n\n");




/**
 * 日志
 */
function ilog($str, $flag = false, $suffix='')
{
    global $domain;
    
    $suffix    = ($suffix ? $suffix . '_' : '');#文件后缀名
    
    $filename = dirname(__FILE__) . '/../logs/upgrade4.0_'. $suffix .'fail_' . date('Y-m-d') . '.log';
    
    if($flag)
    {
        $filename = dirname(__FILE__) . '/../logs/upgrade4.0_'. $suffix .'success_' . date('Y-m-d') . '.log';
    }
    
    $fp = fopen($filename, 'a');
    fwrite($fp, date("m-d H:i") . "\t" . $domain . "\t" . $str . "\n");
    fclose($fp);
}

/**
 * 执行时输入的参数
 */ 
function read_input()
{
    $fp = fopen("/dev/stdin", "r");
    $input = trim(fgets($fp, 255));
    fclose($fp);
    return $input;
}

/**
 * 重新计算指定的单个销售物料对应的店铺冻结
 * @param $shop_id
 * @param $sm_id
 * 
 * return Boolean
 */
function update_sales_material_shop_freeze($shop_id, $sm_id)
{
    global $db;
    
    #先初始化货品对应店铺的冻结数量
    $sql    = "SELECT sm_id FROM sdb_material_sales_material_shop_freeze WHERE sm_id='". $sm_id ."' AND shop_id='". $shop_id ."'";
    $row    = $db->selectrow($sql);
    if(empty($row))
    {
        $sql    = "INSERT INTO `sdb_material_sales_material_shop_freeze` (`sm_id`,`shop_id`,`shop_freeze`) 
                   VALUES('". $sm_id ."','". $shop_id ."',0)";
        $result    = $db->exec($sql);
        if(!$result)
        {
            return false;
        }
    }
    
    #设置店铺冻结数量
    $sql        = "SELECT o.order_id, o.order_bn, o.process_status, o.confirm, j.obj_id, j.obj_type, j.quantity
                   FROM sdb_ome_order_objects AS j LEFT JOIN sdb_ome_orders AS o ON j.order_id=o.order_id
                   WHERE o.process_status in ('unconfirmed', 'is_retrial', 'is_declare', 'splitting') AND o.ship_status in('0', '2')
                   AND o.status='active' AND o.shop_id='". $shop_id ."' AND j.goods_id=".$sm_id;
    $dataList    = $db->select($sql);
    if(empty($dataList))
    {
        return true;
    }
    
    foreach ($dataList as $key => $val)
    {
        #订单部分拆分_但未确认状态
        if($val['confirm'] != 'N' && $val['process_status'] != 'splitting')
        {
            continue;//跳过
        }
        
        #获取订单上基础物料信息_过滤掉删除的货品
        $num         = 0;
        $sql_item    = "SELECT item_id, product_id, nums, sendnum, item_type FROM sdb_ome_order_items 
                        WHERE order_id=". $val['order_id'] ." AND obj_id=". $val['obj_id'] ." AND `delete`='false'";
        $itemRow    = $db->selectrow($sql_item);
        if(empty($itemRow))
        {
            continue;
        }
        
        #销售物料剩余未拆分的数量
        if($val['process_status'] == 'splitting')
        {
            #部分拆分时_直接减去发货单上货品数量
            $dly_sql        = "SELECT SUM(number) AS num FROM `sdb_ome_delivery_items_detail` AS did 
                               JOIN `sdb_ome_delivery` AS d ON d.delivery_id=did.delivery_id 
                               WHERE did.order_item_id='". $itemRow['item_id'] ."' AND did.product_id='". $itemRow['product_id'] ."' 
                               AND d.status NOT IN('back', 'cancel', 'return_back') AND d.is_bind='false'";
            $dly_num        = $db->selectrow($dly_sql);
            $diff_num       = intval($itemRow['nums'] - $dly_num['num']);
        }
        else 
        {
            $diff_num       = intval($itemRow['nums'] - $itemRow['sendnum']);
        }
        
        $num    = intval($diff_num / ($itemRow['nums'] / $val['quantity']));
        if($num)
        {
            #[更新]销售物料店铺冻结
            $update_sql    = "UPDATE sdb_material_sales_material_shop_freeze SET shop_freeze=IFNULL(shop_freeze,0) + ". $num ." 
                              WHERE sm_id='". $sm_id ."' AND shop_id='". $shop_id ."'";
            $result    = $db->exec($update_sql);
            if(!$result)
            {
                return false;
            }
        }
    }
    unset($sql, $sql_item, $dly_sql, $update_sql, $dataList, $dly_num, $diff_num, $num, $itemRow);
    
    return true;
}

/**
 * 系统自动新建PKG促销销售物料
 * @param $param  订单对象信息
 *
 * return Boolean
 */
function sys_create_pkg_sales_material($param)
{
    global $db, $salesMaterialObj, $salesMaterialExtObj;
    
    //系统生成的销售物料编码
    $param['obj_bn']    = 'sys_pkg_'. $param['obj_id'];#使用obj_id为bn,防止商品货号是空_或者是_中文
    
    //判断促销物料编号是否已经被占用
    $sales_material    = $salesMaterialObj->dump(array('sales_material_bn'=>$param['obj_bn'], 'sales_material_type'=>2), 'sm_id, sales_material_bn');
    if($sales_material)
    {
        return $sales_material;
    }
    
    //新建促销销售物料
    $sales_material_bn_crc32    = sprintf('%u',crc32($param['obj_bn']));
    $param['name']    = str_replace("'", "", $param['name']);#替换单引号
    $new_pkg_data = array(
            'shop_id' => '_ALL_',
            'sales_material_name' => $param['name'],
            'sales_material_bn' => $param['obj_bn'],
            'sales_material_bn_crc32' => $sales_material_bn_crc32,
            'sales_material_type' => 2,
            'is_bind' => 1,
            'create_time' => time(),
    );
    $is_save = $salesMaterialObj->save($new_pkg_data);
    if(!$is_save)
    {
        return false;
    }
    
    //新建促销销售的扩展信息
    $new_pkg_ext_data = array(
            'sm_id' => $new_pkg_data['sm_id'],
            'cost' => 0.00,
            'retail_price' => 0.00,
            'weight' => 0.00,
            'unit' => '',
    );
    $is_save    = $salesMaterialExtObj->insert($new_pkg_ext_data);
    if(!$is_save)
    {
        return false;
    }
    
    //销售物料基础物料关联数据结构
    $sql    = "SELECT product_id, nums FROM sdb_ome_order_items WHERE obj_id=". $param['obj_id'];
    $item_list    = $db->select($sql);
    if($item_list)
    {
            
            $pkg_product_list     = array();
            $pkg_product_count    = 0;
            foreach ($item_list as $key => $val)
            {
                $pkg_product_list[$val['product_id']]    = array('pkgnum'=>$val['nums'], 'percent'=>0);
                
                $pkg_product_count += $val['nums'];
            }
            
        
        $percent    = 100;
        $rate       = 100;#默认
        $loop       = 1;
        foreach ($item_list as $key => $val)
        {
            $pkg_product_id    = $val['product_id'];
            
            #计算贡献占比
            if($pkg_product_list)
            {
                if($loop == count($item_list))
                {
                    $pkg_product_list[$pkg_product_id]['percent']    = $percent;
                }
                else
                {
                    $diff_num    = round(($pkg_product_list[$pkg_product_id]['pkgnum'] / $pkg_product_count), 2);
                    $diff_num    = intval($diff_num * 10) * 10;
                    $pkg_product_list[$pkg_product_id]['percent']    = $diff_num;
                    
                    $percent    = $percent - $diff_num;
                    $percent    = ($percent < 0 ? 0 : $percent);
                }
                $loop++;
                
                $rate    = intval($pkg_product_list[$pkg_product_id]['percent']);#取整10的倍数
            }
            
            #拼接数据
            $insert_list[$pkg_product_id]    = "(". $new_pkg_data['sm_id'] .",". $pkg_product_id .",". $pkg_product_list[$pkg_product_id]['pkgnum'] .",". $rate .")";
        }
        
        #插入
        $insert_sql    = "INSERT INTO `sdb_material_sales_basic_material` (`sm_id`,`bm_id`,`number`,`rate`) VALUES";
        $insert_sql    .= implode(',', $insert_list);
        $result        = $db->exec($insert_sql);
        if(!$result)
        {
            return false;
        }
    }
    
    #release
    unset($sql, $insert_sql, $sales_material, $sales_material_bn_crc32, $new_pkg_ext_data, $insert_list, $result, $pkg_product_id, $loop, $rate, $is_save);
    
    return array('sm_id'=>$new_pkg_data['sm_id'], 'sales_material_bn'=>$param['obj_bn']);
}


/**
 * 系统自动新建普通销售物料
 * @param $param  订单对象信息
 *
 * return Boolean
 */
function sys_create_normal_sales_material($param)
{
    global $db, $salesMaterialObj, $salesMaterialExtObj;
    
    //系统生成的销售物料编码
    $param['obj_bn']    = 'sys_normal_'. $param['obj_id'];#使用obj_id为bn,防止商品货号是空_或者是_中文
    
    //单独查询order_objects订单对象表上的name值
    $sql         = "SELECT name FROM sdb_ome_order_objects WHERE obj_id=". $param['obj_id'];
    $obj_info    = $db->selectrow($sql);
    $param['name']    = $obj_info['name'];
    
    //判断普通物料编号是否已经被占用
    $sales_material    = $salesMaterialObj->dump(array('sales_material_bn'=>$param['obj_bn'], 'sales_material_type'=>1), 'sm_id, sales_material_bn');
    if($sales_material)
    {
        return $sales_material;
    }
    
    //新建普通销售物料
    $sales_material_bn_crc32    = sprintf('%u',crc32($param['obj_bn']));
    $param['name']    = str_replace("'", "", $param['name']);#替换单引号
    $new_pkg_data = array(
            'shop_id' => '_ALL_',
            'sales_material_name' => $param['name'],
            'sales_material_bn' => $param['obj_bn'],
            'sales_material_bn_crc32' => $sales_material_bn_crc32,
            'sales_material_type' => 1,
            'is_bind' => 1,
            'create_time' => time(),
    );
    $is_save = $salesMaterialObj->save($new_pkg_data);
    if(!$is_save)
    {
        return false;
    }
    
    //新建促销销售的扩展信息
    $new_pkg_ext_data = array(
            'sm_id' => $new_pkg_data['sm_id'],
            'cost' => 0.00,
            'retail_price' => 0.00,
            'weight' => 0.00,
            'unit' => '',
    );
    $is_save    = $salesMaterialExtObj->insert($new_pkg_ext_data);
    if(!$is_save)
    {
        return false;
    }
    
    //销售物料基础物料关联数据结构
    $sql    = "SELECT product_id, nums FROM sdb_ome_order_items WHERE obj_id=". $param['obj_id'];
    $item_list    = $db->select($sql);
    if($item_list)
    {
            
            $pkg_product_list     = array();
            $pkg_product_count    = 0;
            foreach ($item_list as $key => $val)
            {
                $pkg_product_list[$val['product_id']]    = array('pkgnum'=>$val['nums'], 'percent'=>0);
                
                $pkg_product_count += $val['nums'];
            }
            
        
        $percent    = 100;
        $rate       = 100;#默认
        $loop       = 1;
        foreach ($item_list as $key => $val)
        {
            $pkg_product_id    = $val['product_id'];
            
            #计算贡献占比
            if($pkg_product_list)
            {
                if($loop == count($item_list))
                {
                    $pkg_product_list[$pkg_product_id]['percent']    = $percent;
                }
                else
                {
                    $diff_num    = round(($pkg_product_list[$pkg_product_id]['pkgnum'] / $pkg_product_count), 2);
                    $diff_num    = intval($diff_num * 10) * 10;
                    $pkg_product_list[$pkg_product_id]['percent']    = $diff_num;
                    
                    $percent    = $percent - $diff_num;
                    $percent    = ($percent < 0 ? 0 : $percent);
                }
                $loop++;
                
                $rate    = intval($pkg_product_list[$pkg_product_id]['percent']);#取整10的倍数
            }
            
            #拼接数据
            $insert_list[$pkg_product_id]    = "(". $new_pkg_data['sm_id'] .",". $pkg_product_id .",". $pkg_product_list[$pkg_product_id]['pkgnum'] .",". $rate .")";
        }
        
        #插入
        $insert_sql    = "INSERT INTO `sdb_material_sales_basic_material` (`sm_id`,`bm_id`,`number`,`rate`) VALUES";
        $insert_sql    .= implode(',', $insert_list);
        $result        = $db->exec($insert_sql);
        if(!$result)
        {
            return false;
        }
    }
    
    #release
    unset($sql, $insert_sql, $sales_material, $sales_material_bn_crc32, $new_pkg_ext_data, $insert_list, $result, $pkg_product_id, $loop, $rate, $is_save);
    
    return array('sm_id'=>$new_pkg_data['sm_id'], 'sales_material_bn'=>$param['obj_bn']);
}



/**
 * [归档]系统自动新建PKG促销销售物料
 * @param $param  订单对象信息
 *
 * return Boolean
 */
function archive_sys_create_pkg_sales_material($param)
{
    global $db, $salesMaterialObj, $salesMaterialExtObj;
    
    //系统生成的销售物料编码
    $param['obj_bn']    = 'sys_pkg_'. $param['obj_id'];#使用obj_id为bn,防止商品货号是空_或者是_中文
    
    //判断促销物料编号是否已经被占用
    $sales_material    = $salesMaterialObj->dump(array('sales_material_bn'=>$param['obj_bn'], 'sales_material_type'=>2), 'sm_id, sales_material_bn');
    if($sales_material)
    {
        return $sales_material;
    }
    
    //新建促销销售物料
    $sales_material_bn_crc32    = sprintf('%u',crc32($param['obj_bn']));
    $param['name']    = str_replace("'", "", $param['name']);#替换单引号
    $new_pkg_data = array(
            'shop_id' => '_ALL_',
            'sales_material_name' => $param['name'],
            'sales_material_bn' => $param['obj_bn'],
            'sales_material_bn_crc32' => $sales_material_bn_crc32,
            'sales_material_type' => 2,
            'is_bind' => 1,
            'create_time' => time(),
    );
    $is_save = $salesMaterialObj->save($new_pkg_data);
    if(!$is_save)
    {
        return false;
    }
    
    //新建促销销售的扩展信息
    $new_pkg_ext_data = array(
            'sm_id' => $new_pkg_data['sm_id'],
            'cost' => 0.00,
            'retail_price' => 0.00,
            'weight' => 0.00,
            'unit' => '',
    );
    $is_save    = $salesMaterialExtObj->insert($new_pkg_ext_data);
    if(!$is_save)
    {
        return false;
    }
    
    //销售物料基础物料关联数据结构
    $sql    = "SELECT product_id, nums FROM sdb_archive_order_items WHERE obj_id=". $param['obj_id'];
    $item_list    = $db->select($sql);
    if($item_list)
    {
            $pkg_product_list     = array();
            $pkg_product_count    = 0;
            foreach ($item_list as $key => $val)
            {
                $pkg_product_list[$val['product_id']]    = array('pkgnum'=>$val['nums'], 'percent'=>0);
                
                $pkg_product_count += $val['nums'];
            }
        
        $percent    = 100;
        $rate       = 100;#默认
        $loop       = 1;
        foreach ($item_list as $key => $val)
        {
            $pkg_product_id    = $val['product_id'];
            
            #计算贡献占比
            if($pkg_product_list)
            {
                if($loop == count($item_list))
                {
                    $pkg_product_list[$pkg_product_id]['percent']    = $percent;
                }
                else
                {
                    $diff_num    = round(($pkg_product_list[$pkg_product_id]['pkgnum'] / $pkg_product_count), 2);
                    $diff_num    = intval($diff_num * 10) * 10;
                    $pkg_product_list[$pkg_product_id]['percent']    = $diff_num;
                    
                    $percent    = $percent - $diff_num;
                    $percent    = ($percent < 0 ? 0 : $percent);
                }
                $loop++;
                
                $rate    = intval($pkg_product_list[$pkg_product_id]['percent']);#取整10的倍数
            }
            
            #拼接数据
            $insert_list[$pkg_product_id]    = "(". $new_pkg_data['sm_id'] .",". $pkg_product_id .",". $pkg_product_list[$pkg_product_id]['pkgnum'] .",". $rate .")";
        }
        
        #插入
        $insert_sql    = "INSERT INTO `sdb_material_sales_basic_material` (`sm_id`,`bm_id`,`number`,`rate`) VALUES";
        $insert_sql    .= implode(',', $insert_list);
        $result        = $db->exec($insert_sql);
        if(!$result)
        {
            return false;
        }
    }
    
    #release
    unset($sql, $insert_sql, $sales_material, $sales_material_bn_crc32, $new_pkg_ext_data, $insert_list, $result, $pkg_product_id, $loop, $rate, $is_save);
    
    return array('sm_id'=>$new_pkg_data['sm_id'], 'sales_material_bn'=>$param['obj_bn']);
}



/**
 * [归档]系统自动新建普通销售物料
 * @param $param  订单对象信息
 *
 * return Boolean
 */
function archive_sys_create_normal_sales_material($param)
{
    global $db, $salesMaterialObj, $salesMaterialExtObj;
    
    //系统生成的销售物料编码
    $param['obj_bn']    = 'sys_normal_'. $param['obj_id'];#使用obj_id为bn,防止商品货号是空_或者是_中文
    
    //单独查询order_objects订单对象表上的name值
    $sql         = "SELECT name FROM sdb_archive_order_objects WHERE obj_id=". $param['obj_id'];
    $obj_info    = $db->selectrow($sql);
    $param['name']    = $obj_info['name'];
    
    //判断普通物料编号是否已经被占用
    $sales_material    = $salesMaterialObj->dump(array('sales_material_bn'=>$param['obj_bn'], 'sales_material_type'=>1), 'sm_id, sales_material_bn');
    if($sales_material)
    {
        return $sales_material;
    }
    
    //新建普通销售物料
    $sales_material_bn_crc32    = sprintf('%u',crc32($param['obj_bn']));
    $param['name']    = str_replace("'", "", $param['name']);#替换单引号
    $new_pkg_data = array(
            'shop_id' => '_ALL_',
            'sales_material_name' => $param['name'],
            'sales_material_bn' => $param['obj_bn'],
            'sales_material_bn_crc32' => $sales_material_bn_crc32,
            'sales_material_type' => 1,
            'is_bind' => 1,
            'create_time' => time(),
    );
    $is_save = $salesMaterialObj->save($new_pkg_data);
    if(!$is_save)
    {
        return false;
    }
    
    //新建促销销售的扩展信息
    $new_pkg_ext_data = array(
            'sm_id' => $new_pkg_data['sm_id'],
            'cost' => 0.00,
            'retail_price' => 0.00,
            'weight' => 0.00,
            'unit' => '',
    );
    $is_save    = $salesMaterialExtObj->insert($new_pkg_ext_data);
    if(!$is_save)
    {
        return false;
    }
    
    //销售物料基础物料关联数据结构
    $sql    = "SELECT product_id, nums FROM sdb_archive_order_items WHERE obj_id=". $param['obj_id'];
    $item_list    = $db->select($sql);
    if($item_list)
    {
            $pkg_product_list     = array();
            $pkg_product_count    = 0;
            foreach ($item_list as $key => $val)
            {
                $pkg_product_list[$val['product_id']]    = array('pkgnum'=>$val['nums'], 'percent'=>0);
                
                $pkg_product_count += $val['nums'];
            }
        
        $percent    = 100;
        $rate       = 100;#默认
        $loop       = 1;
        foreach ($item_list as $key => $val)
        {
            $pkg_product_id    = $val['product_id'];
            
            #计算贡献占比
            if($pkg_product_list)
            {
                if($loop == count($item_list))
                {
                    $pkg_product_list[$pkg_product_id]['percent']    = $percent;
                }
                else
                {
                    $diff_num    = round(($pkg_product_list[$pkg_product_id]['pkgnum'] / $pkg_product_count), 2);
                    $diff_num    = intval($diff_num * 10) * 10;
                    $pkg_product_list[$pkg_product_id]['percent']    = $diff_num;
                    
                    $percent    = $percent - $diff_num;
                    $percent    = ($percent < 0 ? 0 : $percent);
                }
                $loop++;
                
                $rate    = intval($pkg_product_list[$pkg_product_id]['percent']);#取整10的倍数
            }
            
            #拼接数据
            $insert_list[$pkg_product_id]    = "(". $new_pkg_data['sm_id'] .",". $pkg_product_id .",". $pkg_product_list[$pkg_product_id]['pkgnum'] .",". $rate .")";
        }
        
        #插入
        $insert_sql    = "INSERT INTO `sdb_material_sales_basic_material` (`sm_id`,`bm_id`,`number`,`rate`) VALUES";
        $insert_sql    .= implode(',', $insert_list);
        $result        = $db->exec($insert_sql);
        if(!$result)
        {
            return false;
        }
    }
    
    #release
    unset($sql, $insert_sql, $sales_material, $sales_material_bn_crc32, $new_pkg_ext_data, $insert_list, $result, $pkg_product_id, $loop, $rate, $is_save);
    
    return array('sm_id'=>$new_pkg_data['sm_id'], 'sales_material_bn'=>$param['obj_bn']);
}
